﻿Imports System
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Data.SqlClient
Imports System.Linq
Imports System.Web
Imports Forecast.Logica.Enumeradores
Imports Forecast.Logica.Modelos
Namespace Servicios
    Public Class ServForecast

        Public Function Search_ForeCastDeUsuario(IdUsuario As Integer, IdEntity As Integer, _BusForecast As BusForecast) As DataTable
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet

            Dim ConnString As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "FC_ForeCast_Search_DeUsuario"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@IdUsuario", Data.SqlDbType.Int)
            cmd.Parameters("@IdUsuario").Value = IdUsuario

            cmd.Parameters.Add("@IdEntity", Data.SqlDbType.Int)
            cmd.Parameters("@IdEntity").Value = IdEntity

            '------------------------------------------
            cmd.Parameters.Add("@IdCustomerB", Data.SqlDbType.Int)
            cmd.Parameters("@IdCustomerB").Value = _BusForecast.IdCustomer
            cmd.Parameters.Add("@IdUOMB", Data.SqlDbType.Int)
            cmd.Parameters("@IdUOMB").Value = _BusForecast.IdUOM
            cmd.Parameters.Add("@IdProductB", Data.SqlDbType.Int)
            cmd.Parameters("@IdProductB").Value = _BusForecast.IdProduct
            cmd.Parameters.Add("@IdSegmentoB", Data.SqlDbType.Int)
            cmd.Parameters("@IdSegmentoB").Value = _BusForecast.IdSegmento
            cmd.Parameters.Add("@IdProductLineB", Data.SqlDbType.Int)
            cmd.Parameters("@IdProductLineB").Value = _BusForecast.IdProductLine
            cmd.Parameters.Add("@CodCustomerB", Data.SqlDbType.VarChar)
            cmd.Parameters("@CodCustomerB").Value = _BusForecast.CodCustomer

            '------------------------------------------
            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
            Return ds.Tables(0)
        End Function

        Public Function Search_InfoPeriodos(IdProduct As Integer, IdCustomer As Integer, IdSegmentPL As Integer, Activos As Integer) As DataTable
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet

            Dim ConnString As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "FC_Forescast_Search_PorPeriodo"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@IdProduct", Data.SqlDbType.Int)
            cmd.Parameters("@IdProduct").Value = IdProduct

            cmd.Parameters.Add("@IdCustomer", Data.SqlDbType.Int)
            cmd.Parameters("@IdCustomer").Value = IdCustomer

            cmd.Parameters.Add("@IdSegmentPL", Data.SqlDbType.Int)
            cmd.Parameters("@IdSegmentPL").Value = IdSegmentPL

            cmd.Parameters.Add("@Activos", Data.SqlDbType.Int)
            cmd.Parameters("@Activos").Value = Activos

            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
            Return ds.Tables(0)
        End Function



        Public Function Read_Forecast(IdCustomer As Integer, IdProduct As Integer) As DataTable
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet

            Dim ConnString As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "FC_Forecast_Read"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@IdCustomer", Data.SqlDbType.Int)
            cmd.Parameters("@IdCustomer").Value = IdCustomer

            cmd.Parameters.Add("@IdProduct", Data.SqlDbType.Int)
            cmd.Parameters("@IdProduct").Value = IdProduct


            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
            Return ds.Tables(0)
        End Function
        Public Sub GuardarForecast(Forecast As ModForecast)
            If Forecast.IdForecast = 0 Then
                IngresarNewForecast(Forecast)
            Else
                ModificarForecast(Forecast)
            End If
        End Sub

        Private Sub IngresarNewForecast(Forecast As ModForecast)
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim Transaccion As SqlTransaction

            Dim ConnString As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cn.Open()
            Transaccion = cn.BeginTransaction()

            Try
                ' ----CREAR PRODUCTO
                If Forecast.IdProduct = 99999999 Then
                    cmd = New SqlCommand()
                    cmd.Connection = cn
                    cmd.Transaction = Transaccion

                    cmd.CommandText = "FC_Product_Inset"
                    Dim RetVal2 As SqlParameter = cmd.Parameters.Add("Return", Data.SqlDbType.Int)
                    RetVal2.Direction = Data.ParameterDirection.ReturnValue

                    cmd.CommandType = CommandType.StoredProcedure

                    cmd.Parameters.Add("@CodProduct", Data.SqlDbType.VarChar)
                    cmd.Parameters("@CodProduct").Value = Forecast.IdProduct

                    cmd.Parameters.Add("@IdSegmentPL", Data.SqlDbType.Int)
                    cmd.Parameters("@IdSegmentPL").Value = Forecast.IdSegment

                    cmd.Parameters.Add("@DesProduct", Data.SqlDbType.VarChar)
                    cmd.Parameters("@DesProduct").Value = Forecast.NameProductOther

                    cmd.Parameters.Add("@IdEntityF", Data.SqlDbType.Int)
                    cmd.Parameters("@IdEntityF").Value = Forecast.IdEntityF

                    cmd.Parameters.Add("@IdUOM", Data.SqlDbType.Int)
                    cmd.Parameters("@IdUOM").Value = Forecast.IdEntityF

                    cmd.ExecuteNonQuery()
                    Forecast.IdProduct = cmd.Parameters("Return").Value

                End If
                '----- CREAR CLIENTE
                If Forecast.IdCustomer = 99999999 Then
                    cmd = New SqlCommand()
                    cmd.Connection = cn
                    cmd.Transaction = Transaccion

                    cmd.CommandText = "FC_Customer_Insert"
                    Dim RetVal2 As SqlParameter = cmd.Parameters.Add("Return", Data.SqlDbType.Int)
                    RetVal2.Direction = Data.ParameterDirection.ReturnValue

                    cmd.CommandType = CommandType.StoredProcedure

                    cmd.Parameters.Add("@Codigo", Data.SqlDbType.VarChar)
                    cmd.Parameters("@Codigo").Value = Forecast.IdCustomer

                    cmd.Parameters.Add("@Name", Data.SqlDbType.VarChar)
                    cmd.Parameters("@Name").Value = Forecast.NameCustomerOther

                    cmd.Parameters.Add("@IdEntityF", Data.SqlDbType.VarChar)
                    cmd.Parameters("@IdEntityF").Value = Forecast.IdEntityF

                    'cmd.Parameters.Add("@IdCoutry", Data.SqlDbType.VarChar)
                    'cmd.Parameters("@IdCoutry").Value = Forecast.NameCustomerOther

                    cmd.ExecuteNonQuery()
                    Forecast.IdCustomer = cmd.Parameters("Return").Value

                    '---------------------- SalePrice --------------------
                    cmd = New SqlCommand()
                    cmd.Connection = cn
                    cmd.Transaction = Transaccion
                    cmd.CommandText = "FC_SalePrice_Insert"
                    cmd.CommandType = CommandType.StoredProcedure

                    cmd.Parameters.Add("@IdCustomer", Data.SqlDbType.Int)
                    cmd.Parameters("@IdCustomer").Value = Forecast.IdCustomer

                    cmd.Parameters.Add("@IdProduct", Data.SqlDbType.Int)
                    cmd.Parameters("@IdProduct").Value = Forecast.IdProduct

                    cmd.Parameters.Add("@Value", Data.SqlDbType.Float)
                    cmd.Parameters("@Value").Value = Forecast.ValueProduct

                    cmd.Parameters.Add("@Currency", Data.SqlDbType.VarChar)
                    cmd.Parameters("@Currency").Value = Forecast.Currency
                    cmd.ExecuteNonQuery()

                End If

                '---------------------------------------------------------------------------------

                cmd = New SqlCommand()
                cmd.Connection = cn
                cmd.Transaction = Transaccion

                cmd.CommandText = "FC_Forecast_Insert"
                Dim RetVal As SqlParameter = cmd.Parameters.Add("Return", Data.SqlDbType.Int)
                RetVal.Direction = Data.ParameterDirection.ReturnValue

                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("@IdUsuario", Data.SqlDbType.Int)
                cmd.Parameters("@IdUsuario").Value = Forecast.IdUsuario

                cmd.Parameters.Add("@IdCustomer", Data.SqlDbType.Int)
                cmd.Parameters("@IdCustomer").Value = Forecast.IdCustomer

                cmd.Parameters.Add("@IdPeriod", Data.SqlDbType.Int)
                cmd.Parameters("@IdPeriod").Value = Forecast.IdPeriod

                cmd.Parameters.Add("@IdProduct", Data.SqlDbType.Int)
                cmd.Parameters("@IdProduct").Value = Forecast.IdProduct

                cmd.Parameters.Add("@Special", Data.SqlDbType.Bit)
                cmd.Parameters("@Special").Value = Forecast.Special

                cmd.Parameters.Add("@Volume", Data.SqlDbType.Float)
                cmd.Parameters("@Volume").Value = Forecast.Volume

                cmd.ExecuteNonQuery()
                Forecast.IdForecast = cmd.Parameters("Return").Value

                '---------------------- ACTUALIZAR --------------------
                cmd = New SqlCommand()
                cmd.Connection = cn
                cmd.Transaction = Transaccion
                cmd.CommandText = "FC_InformeForeCast_Insert"
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("@IdForecast ", Data.SqlDbType.Float)
                cmd.Parameters("@IdForecast ").Value = Forecast.IdForecast

                cmd.ExecuteNonQuery()


                Transaccion.Commit()

            Catch ex As Exception

            End Try

        End Sub
        Function VerificarExistenciaForecast(Forecast As ModForecast) As DataTable
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet

            Dim ConnString As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cmd = New SqlCommand()
            cmd.Connection = cn
            cmd.CommandText = "FC_Forecast_Search_PorClienteProducto"
            cmd.CommandType = CommandType.StoredProcedure

            cmd.Parameters.Add("@IdCustomer", Data.SqlDbType.Int)
            cmd.Parameters("@IdCustomer").Value = Forecast.IdCustomer

            cmd.Parameters.Add("@IdProduct", Data.SqlDbType.Int)
            cmd.Parameters("@IdProduct").Value = Forecast.IdProduct


            da = New SqlDataAdapter()
            da.SelectCommand = cmd
            ds = New DataSet()
            da.Fill(ds)
            Return ds.Tables(0)
        End Function
        Private Sub ModificarForecast(Forecast As ModForecast)
            Dim cn As New SqlConnection()
            Dim cmd As New SqlCommand()
            Dim da As SqlDataAdapter
            Dim ds As DataSet
            Dim Transaccion As SqlTransaction

            Dim ConnString As String = ConfigurationManager.ConnectionStrings("ConnStr").ConnectionString.ToString
            cn.ConnectionString = ConnString
            cn.Open()
            Transaccion = cn.BeginTransaction()

            Try
                cmd = New SqlCommand()
                cmd.Connection = cn
                cmd.Transaction = Transaccion

                cmd.CommandText = "FC_Forecast_Update"
                Dim RetVal As SqlParameter = cmd.Parameters.Add("Return", Data.SqlDbType.Int)
                RetVal.Direction = Data.ParameterDirection.ReturnValue

                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("@IdForecast", Data.SqlDbType.Int)
                cmd.Parameters("@IdForecast").Value = Forecast.IdForecast

                cmd.Parameters.Add("@IdUsuario", Data.SqlDbType.Int)
                cmd.Parameters("@IdUsuario").Value = Forecast.IdUsuario

                cmd.Parameters.Add("@IdCustomer", Data.SqlDbType.Int)
                cmd.Parameters("@IdCustomer").Value = Forecast.IdCustomer

                cmd.Parameters.Add("@IdPeriod", Data.SqlDbType.Int)
                cmd.Parameters("@IdPeriod").Value = Forecast.IdPeriod

                cmd.Parameters.Add("@IdProduct", Data.SqlDbType.Int)
                cmd.Parameters("@IdProduct").Value = Forecast.IdProduct

                cmd.Parameters.Add("@Special", Data.SqlDbType.Bit)
                cmd.Parameters("@Special").Value = Forecast.Special

                cmd.Parameters.Add("@Volume", Data.SqlDbType.Float)
                cmd.Parameters("@Volume").Value = Forecast.Volume

                cmd.ExecuteNonQuery()

                '---------------------- ACTUALIZAR --------------------
                cmd = New SqlCommand()
                cmd.Connection = cn
                cmd.Transaction = Transaccion
                cmd.CommandText = "FC_InformeForeCast_Insert"
                cmd.CommandType = CommandType.StoredProcedure

                cmd.Parameters.Add("@IdForecast ", Data.SqlDbType.Float)
                cmd.Parameters("@IdForecast ").Value = Forecast.IdForecast

                cmd.ExecuteNonQuery()


                Forecast.IdForecast = Forecast.IdForecast
                Transaccion.Commit()

            Catch ex As Exception

            End Try

        End Sub

    End Class
End Namespace

